package org.madmac.setor;


import org.madmac.maquina.MaquinaQuery;
import org.wakeful.conn.Connection;
import org.wakeful.query.Query;
/**
 *
 * @author atila camurca
 */
public final class SetorQuery {

	public static final String TABLE = Connection.getSchema().concat(Query.DOT).concat("setor");
   public static final String ALIAS = TABLE.concat(" s");

	private SetorQuery() {
	}
	
	public static String create() {
      StringBuilder builder = new StringBuilder();
		builder.append(Query.INSERT);
      builder.append(SetorQuery.TABLE);
      builder.append(" (nome, ramal)");
		builder.append(Query.VALUES);
      builder.append("(UPPER(?), ?)");
		builder.append(Query.RETURNING);
      builder.append("id_setor");
      return builder.toString();
   }
   
   public static String retrieve() {
      StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
      builder.append("id_setor, nome, ramal");
		builder.append(Query.FROM);
		builder.append(SetorQuery.TABLE);
		builder.append(Query.WHERE);
      builder.append("id_setor = ?");
      return builder.toString();
   }
   
   public static String update() {
      StringBuilder builder = new StringBuilder();
      builder.append("");
      return builder.toString();
   }
   
   public static String delete() {
      StringBuilder builder = new StringBuilder();
      builder.append("");
      return builder.toString();
   }

   public static String findAll() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.SELECT);
      builder.append("s.id_setor, s.nome, s.ramal, count(ma.id_maquina) AS num_maquinas");
      builder.append(Query.FROM);
      builder.append(SetorQuery.ALIAS);
      builder.append(Query.LEFT_JOIN);
      builder.append(MaquinaQuery.ALIAS);
      builder.append(Query.ON);
      builder.append("ma.id_setor = s.id_setor ");
      builder.append(Query.GROUP_BY);
      builder.append("s.id_setor, s.nome, s.ramal");
      builder.append(Query.ORDER_BY);
      builder.append("s.nome");
      return builder.toString();
   }
}
